<?php

/**
 * SocialEngine
 *
 * @category   Application_Extensions
 * @package    Sitepagereview
 * @copyright  Copyright 2010-2011 BigStep Technologies Pvt. Ltd.
 * @license    http://www.socialengineaddons.com/license/
 * @version    $Id: Reviews.php 2011-05-05 9:40:21Z SocialEngineAddOns $
 * @author     SocialEngineAddOns
 */
class Sitepagereview_Model_DbTable_Reviews extends Engine_Db_Table {
	
	protected $_rowClass = "Sitepagereview_Model_Review";
	
	/**
	 * Return review data for checking that viewer has been posted a review or not
	 *
	 * @param Int page_id
	 * @param Int viewer_id
	 * @return Zend_Db_Table_Select
	 */
	public function canPostReview($page_id, $viewer_id) {
		
		//MAKE QUERY
		$hasPosted = $this->select ()->from ( $this->info ( 'name' ), array ('review_id' ) )->where ( 'page_id = ?', $page_id )->where ( 'owner_id = ?', $viewer_id )->query ()->fetchColumn ();
		
		//RETURN RESULTS
		return $hasPosted;
	}
	
	/**
	 * Return average recommendetion for page reviews
	 *
	 * @param Int page_id
	 * @return Zend_Db_Table_Select
	 */
	public function getAvgRecommendation($page_id) {
		
		//MAKE QUERY
		$select = $this->select ()->from ( $this->info ( 'name' ), array ('*', 'AVG(recommend) AS avg_recommend' ) )->where ( 'page_id = ?', $page_id )->group ( 'page_id' );
		
		//RETURN RESULTS
		return $this->fetchAll ( $select );
	}
	
	/**
	 * Return total reviews for page
	 *
	 * @param Int page_id
	 * @return Zend_Db_Table_Select
	 */
	public function totalReviews($page_id) {
		
		//MAKE QUERY
		$totalReviews = $this->select ()->from ( $this->info ( 'name' ), array ('COUNT(*) AS count' ) )->where ( 'page_id = ?', $page_id )->query ()->fetchColumn ();
		
		//RETURN RESULTS
		return $totalReviews;
	}
	
	/**
	 * Return page reviews
	 *
	 * @param Int page_id
	 * @return Zend_Db_Table_Select
	 */
	public function pageReviews($page_id) {
		
		//MAKE QUERY
		$select = $this->select ()->where ( 'page_id = ?', $page_id )->order ( 'modified_date DESC' );
		
		//RETURN RESULTS
		return Zend_Paginator::factory ( $select );
	}
	
	/**
	 * Get reviews to add as item of the day
	 * @param string $title : search text
	 * @param int $limit : result limit
	 */
	public function getDayItems($search_text, $limit = 10) {
		
		//GET PAGE TABLE NAME
		$pageTableName = Engine_Api::_ ()->getDbTable ( 'pages', 'sitepage' )->info ( 'name' );
		
		//GET ITEM TABLE NAME
		$itemTableName = $this->info ( 'name' );
		
		//MAKE QUERY
		$select = $this->select ()->setIntegrityCheck ( false )->from ( $itemTableName, array ('review_id', 'title', 'owner_id', 'page_id' ) )->joinLeft ( $pageTableName, "$pageTableName.page_id = $itemTableName.page_id", array ('' ) )->where ( $pageTableName . '.closed = ?', '0' )->where ( $pageTableName . '.declined = ?', '0' )->where ( $pageTableName . '.approved = ?', '1' )->where ( $pageTableName . '.draft = ?', '1' )->where ( $itemTableName . '.title  LIKE ? ', '%' . $search_text . '%' )->order ( $itemTableName . '.title ASC' )->limit ( $limit );
		
		//RETURN DATA
		return $this->fetchAll ( $select );
	}
	
	/**
	 * Return page reviews
	 *
	 * @param array params
	 * @return Zend_Db_Table_Select
	 */
	public function reviewRatingData($params = array()) {
		
		//GET PAGE TABLE NAME
		$tablePage = Engine_Api::_ ()->getDbtable ( 'pages', 'sitepage' );
		$tablePageName = Engine_Api::_ ()->getDbtable ( 'pages', 'sitepage' )->info ( 'name' );
		
		//GET RATING TABLE NAME
		$tableRatingName = Engine_Api::_ ()->getDbtable ( 'ratings', 'sitepagereview' )->info ( 'name' );
		
		//GET REVIEW TABLE NAME
		$tableReviewName = $this->info ( 'name' );
		
		//MAKE QUERY
		$select = $this->select ()->setIntegrityCheck ( false )->from ( $tableReviewName )->joinLeft ( $tableRatingName, "$tableRatingName.review_id = $tableReviewName.review_id", array ('rating' ) );
		
		if (isset ( $params ['page_id'] ) && ! empty ( $params ['page_id'] )) {
			$select = $select->where ( $tableReviewName . '.page_id = ?', $params ['page_id'] );
		}
		
		if (isset ( $params ['zero_count'] ) && ! empty ( $params ['zero_count'] )) {
			$select = $select->where ( $tableReviewName . '.' . $params ['zero_count'] . '!= ?', 0 );
		}
		
		if (isset ( $params ['orderby'] ) && ! empty ( $params ['orderby'] )) {
			$select = $select->order ( $tableReviewName . '.' . $params ['orderby'] );
		}
		
		$select = $select->order ( $tableReviewName . '.review_id DESC' )->where ( $tableRatingName . '.reviewcat_id = ?', 0 );
		
		if (isset ( $params ['limit'] ) && ! empty ( $params ['limit'] )) {
			$select = $select->limit ( $params ['limit'] );
		}
		
		if (isset ( $params ['page_validation'] ) && ! empty ( $params ['page_validation'] )) {
			
			$select = $select->joinLeft ( $tablePageName, "$tablePageName.page_id = $tableReviewName.page_id", array ('page_id', 'title AS page_title', 'photo_id' ) )->where ( $tablePageName . '.search = ?', '1' )->where ( $tablePageName . '.closed = ?', '0' )->where ( $tablePageName . '.approved = ?', '1' )->where ( $tablePageName . '.declined = ?', '0' )->where ( $tablePageName . '.draft = ?', '1' );
			if (Engine_Api::_ ()->sitepage ()->hasPackageEnable ()) {
				$select->where ( $tablePageName . '.expiration_date  > ?', date ( "Y-m-d H:i:s" ) );
			}
		}
		//Start Network work
		if (! isset ( $params ['page_id'] ) || empty ( $params ['page_id'] )) {
			//    $select = $tablePage->getNetworkBaseSql($select, array('not_groupBy' => 1, 'extension_group' => $tableReviewName . ".review_id"));
		}
		//End Network work
		//RETURN RESULTS
		if (isset ( $params ['featured'] ) && ! empty ( $params ['featured'] )) {
			$select = $select->where ( $tableReviewName . '.featured = ?', 1 );
			return Zend_Paginator::factory ( $select );
		} else {
			return $this->fetchAll ( $select );
		}
	}
	
	/**
	 * Return review of the day
	 *
	 * @return Zend_Db_Table_Select
	 */
	public function reviewOfDay() {
		
		//CURRENT DATE TIME
		$date = date ( 'Y-m-d' );
		
		//GET ITEM OF THE DAY TABLE NAME
		$reviewOfTheDayTableName = Engine_Api::_ ()->getDbtable ( 'itemofthedays', 'sitepage' )->info ( 'name' );
		
		//GET REVIEW TABLE NAME
		$reviewTableName = $this->info ( 'name' );
		
		//MAKE QUERY
		$select = $this->select ()->setIntegrityCheck ( false )->from ( $reviewTableName, array ('review_id', 'title', 'page_id', 'owner_id', 'body' ) )->join ( $reviewOfTheDayTableName, $reviewTableName . '.review_id = ' . $reviewOfTheDayTableName . '.resource_id' )->where ( 'resource_type = ?', 'sitepagereview_review' )->where ( 'start_date <= ?', $date )->where ( 'end_date >= ?', $date )->order ( 'Rand()' );
		
		//RETURN RESULTS
		return $this->fetchRow ( $select );
	}
	
	/**
	 * Return top reviewers
	 *
	 * @param int itemCount
	 * @return Zend_Db_Table_Select
	 */
	public function topReviewers($itemCount) {
		
		//GET USER TABLE INFO
		$tableUser = Engine_Api::_ ()->getDbtable ( 'users', 'user' );
		$tableUserName = $tableUser->info ( 'name' );
		
		//GET REVIEW TABLE NAME
		$tableReviewName = $this->info ( 'name' );
		
		//MAKE QUERY
		$select = $tableUser->select ()->setIntegrityCheck ( false )->from ( $tableUserName, array ('user_id', 'username', 'photo_id' ) )->join ( $tableReviewName, "$tableUserName.user_id = $tableReviewName.owner_id", array ('COUNT(review_id) AS review_count', 'MAX(review_id) as max_review_id' ) )->group ( $tableUserName . ".user_id" )->order ( 'review_count DESC' )->order ( 'user_id DESC' )->limit ( $itemCount );
		
		//RETURN THE RESULTS
		return $tableUser->fetchAll ( $select );
	}

}
?>